package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;



import crm.dbcoon.DBConnect;
import crm.model.Linkman;
import crm.model.Salearea;
import crm.util.Pagination;

public class LinkmanControl {

	public static Pagination pageInstance = null;
	
	
	public static String getLinkmanName(int id){
		DBConnect dbc = new DBConnect();
		String sql = "SELECT name FROM info_linkman WHERE id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			return rs.getString("name");
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	
	
	public static boolean addLinkman(Linkman li){
		DBConnect dbc=new DBConnect();
		String sql = "insert into info_linkman (customer_id,name,position,email,phone,hobby,province_id,qq,birthday,gender)values (?,?,?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try
		{	
			dbc.setInt(1,li.getCustomer_id());
			dbc.setString(2,li.getName());
			dbc.setString(3, li.getPosition());
			dbc.setString(4, li.getEmail());
			dbc.setString(5, li.getPhone());
			dbc.setString(6, li.getHobby());
			dbc.setInt(7, li.getProvince());
			dbc.setString(8, li.getQq());
			dbc.setString(9, li.getBirthday());
			dbc.setString(10, li.getGender());
			dbc.executeUpdate();
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	public static boolean deleteLinkman(int id){
		DBConnect dbc = new DBConnect();
		String sql = "DELETE FROM info_linkman WHERE id = " + id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	
	public static boolean updateLinkman(Linkman li){
		DBConnect dbc = new DBConnect();
		String sql = "update info_linkman set customer_id=?,name = ?, position = ? , email= ?, phone= ?, hobby  = ?, province_id = ?, qq = ?,birthday = ?,gender = ? where id = '"+li.getId()+"'";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, li.getCustomer_id());
			dbc.setString(2, li.getName());
			dbc.setString(3,li.getPosition());
			dbc.setString(4, li.getEmail());
			dbc.setString(5, li.getPhone());
			dbc.setString(6, li.getHobby());
			dbc.setInt(7, li.getProvince());
			dbc.setString(8, li.getQq());
			dbc.setString(9, li.getBirthday());
			dbc.setString(10, li.getGender());
	        dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			dbc.close();
		}
	}
	
	public static Linkman getLinkmanById(int id){
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM info_linkman WHERE id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			Linkman li = new Linkman();
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
				li.setId(rs.getInt("id"));
				li.setCustomer_id(rs.getInt("customer_id"));
				li.setName(rs.getString("name"));
				li.setPosition(rs.getString("position"));
				li.setEmail(rs.getString("email"));
				li.setPhone(rs.getString("phone"));
				li.setHobby(rs.getString("hobby"));
				li.setProvince(rs.getInt("province_id"));
				li.setQq(rs.getString("qq"));
				li.setBirthday(rs.getString("birthday"));
				li.setGender(rs.getString("gender"));
				return li;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	
	public static Collection<Linkman> getOneCustomerLinkmans(int customer_id) {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		Collection<Linkman> linkmans = new ArrayList<Linkman>();
		String sql = "select * from info_linkman where customer_id='"+customer_id+"'";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				Linkman linkman = new Linkman();
				linkman.setId(rs.getInt("id"));
				   linkman.setCustomer_id(rs.getInt("customer_id"));
				   linkman.setName(rs.getString("name"));
				   linkman.setPosition(rs.getString("position"));
				   linkman.setEmail(rs.getString("email"));
				   linkman.setPhone(rs.getString("phone"));
				   linkman.setHobby(rs.getString("hobby"));
				   linkman.setProvince(rs.getInt("province_id"));
				   linkman.setQq(rs.getString("qq"));
				   linkman.setBirthday(rs.getString("birthday"));
				   linkman.setGender(rs.getString("gender"));
				   linkmans.add(linkman);
			}
			System.out.println(linkmans.size());
		} catch (SQLException e) {
			System.out.println("select fail！\n" + e.getMessage());
			return null;
		} finally {
			dbc.close();
		}
		return linkmans;
	}
	
	/*public static Collection getAllProvince() {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		Collection provinces = new ArrayList();
		String sql = "select id,provinceName from param_province";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				Linkman linkman = new Linkman();
				linkman.setId(rs.getInt(1));
				linkman.setName(rs.getString(2));
				linkmans.add(linkman);
			}
			System.out.println(linkmans.size());
		} catch (SQLException e) {
			System.out.println("select fail！\n" + e.getMessage());
			return null;
		} finally {
			dbc.close();
		}
		return linkmans;
	}*/
	
	public static HashMap getAllLinkman1() {
	     DBConnect dbConnect =new DBConnect();
	     ResultSet rs = null;
	     HashMap linkmans = new HashMap();
	     String sql = "select * from info_linkman";
	     dbConnect.prepareStatement(sql);
	     try {
			rs = dbConnect.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				linkmans.put(id, name);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return linkmans;
	}
	
   public static ArrayList getAllLinkmans() {
		
		String sql = "select * from info_linkman order by id";
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		ArrayList<Linkman> linkmans=new ArrayList();
		dbc.prepareStatement(sql);
		try {						
			rs = dbc.executeQuery();
			while(rs.next())
			{
			   Linkman linkman=new Linkman();
			   linkman.setId(rs.getInt("id"));
			   linkman.setCustomer_id(rs.getInt("customer_id"));
			   linkman.setName(rs.getString("name"));
			   linkman.setPosition(rs.getString("position"));
			   linkman.setEmail(rs.getString("email"));
			   linkman.setPhone(rs.getString("phone"));
			   linkman.setHobby(rs.getString("hobby"));
			   linkman.setProvince(rs.getInt("province_id"));
			   linkman.setQq(rs.getString("qq"));
			   linkman.setBirthday(rs.getString("birthday"));
			   linkman.setGender(rs.getString("gender"));
			   linkmans.add(linkman);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// ������һ��Ҫע�������ݿ�����
			dbc.close(rs);
			dbc.close();
		}
		return linkmans;
	}
	
	
	public Collection getLinkman(int page, int rowsPerPage) throws SQLException, Exception
	{
		String sql = "SELECT * FROM info_linkman";
		pageInstance = new LinkmanControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}
	
	public Collection getLinkman(int page, int rowsPerPage,int customer_id) throws SQLException, Exception
	{
		String sql = "select * from info_linkman where customer_id='"+customer_id+"'";
		pageInstance = new LinkmanControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}
	
	public Collection getLinkman_1(int page, int rowsPerPage,String type,String key) throws SQLException, Exception
	{
		String sql = "SELECT * FROM info_linkman where "+type+" like '%"+key+"%' ";
		System.out.println(sql);
		pageInstance = new LinkmanControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}
}



class LinkmanControlPage extends Pagination {
	public LinkmanControlPage(String sql, int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	@Override
	protected Collection<Linkman> packResultSet(ResultSet rs) throws SQLException {
		Collection<Linkman> linkmans = new ArrayList<Linkman>();
		
		while (rs.next()) {
			Linkman li = new Linkman();
			li.setId(rs.getInt("id"));
			li.setCustomer_id(rs.getInt("customer_id"));
			li.setName(rs.getString("name"));
			li.setPosition(rs.getString("position"));
			li.setEmail(rs.getString("email"));
			li.setPhone(rs.getString("phone"));
			li.setHobby(rs.getString("hobby"));
			li.setProvince(rs.getInt("province_id"));
			li.setQq(rs.getString("qq"));
			li.setBirthday(rs.getString("birthday"));
			li.setGender(rs.getString("gender"));
			linkmans.add(li);
			li=null;
		}
		return linkmans;
	}
}
	
	


